<?php

namespace helper;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class ExcelHelper {

    /**
     * 导出
     * @param array $column
     * @param array $data
     * @param string $file
     * @param string $writeType
     * @throws \Exception
     */
    static function export($column = [], $data = [], $file = '', $writeType = 'xlsx') {
        $fileName    = empty($file) ? date('YmdHis') : $file;
        $spreadsheet = new Spreadsheet();
        //横向单元格标识
        $cellName = array(
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
            'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH',
            'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW',
            'AX', 'AY', 'AZ'
        );
        $sheet    = $spreadsheet->getActiveSheet();
        $types    = [];
        $fields   = [];
        if ($column) {
            foreach ($column AS $k => $v) {   //设置列标题
                //处理列宽
                if (isset($v['width'])) {
                    $sheet->getColumnDimension($cellName[$k])->setWidth($v['width']);
                } else {
                    $sheet->getColumnDimension($cellName[$k])->setAutoSize(true);
                }
                $sheet->setCellValue("{$cellName[$k]}1", $v['title']);
                $types[$k] = empty($v['type']) ? 's' : $v['type']; //s,f,n,b
            }
            $fields = array_column($column, 'field');
        }
        //填写数据
        if ($data) {
            $i = 2;
            foreach ($data AS $_v) {
                $j = 0;
                foreach ($_v AS $_key => $_cell) {
                    if (!empty($column) && !in_array($_key, $fields)) continue;
                    $sheet->setCellValueExplicit("{$cellName[$j]}{$i}", $_cell, "{$types[$j]}");
                    $j++;
                }
                $i++;
            }
        }
        $ext      = strtolower($writeType);
        $objWrite = IOFactory::createWriter($spreadsheet, ucfirst($writeType));
        if ($ext == 'csv') $objWrite->setUseBOM(true);
        ob_end_clean();
        header('pragma:public');
        header("Content-Disposition:attachment;filename={$fileName}.{$ext}");
        $objWrite->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    /**
     * 导入文件
     * @param $file
     * @param int $startRow
     * @param null $inputCoding
     * @return array
     * @throws \Exception
     */
    static public function import($file, $startRow = 2, $inputCoding = null) {
        ini_set('max_execution_time', '0');
        if (empty($file) OR !file_exists($file)) exception('文件不存在');
        $inputFileType = IOFactory::identify($file); //传入文件路径
        $excelReader   = IOFactory::createReader($inputFileType);
        if ($inputFileType == 'Csv' && $inputCoding !== null) {
            $excelReader->setInputEncoding($inputCoding); //GBK乱码解决
        }
        $excelReader->setReadDataOnly(true); // 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率
        $PHPExcel = $excelReader->load($file); // 载入excel文件
        $sheet    = $PHPExcel->getSheet(0); // 读取第一個工作表
        //获取总列数
        $allColumn = $sheet->getHighestColumn();
        //获取总行数
        $allRow = $sheet->getHighestRow();
        ++$allColumn;
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        $data = [];
        for ($currentRow = $startRow; $currentRow <= $allRow; $currentRow++) {
            //从哪列开始，A表示第一列
            for ($currentColumn = 'A'; $currentColumn != $allColumn; $currentColumn++) {
                //数据坐标
                $address = $currentColumn . $currentRow;
                //读取到的数据，保存到数组$arr中
                $value               = trim($sheet->getCell($address)->getValue());
                $data[$currentRow][] = $value;
            }
        }
        return $data;
    }
}